package cn.edu.sdjzu.xg.bysj.dao;

import cn.edu.sdjzu.xg.bysj.domain.Condition;
import cn.edu.sdjzu.xg.bysj.domain.Pagination;
import cn.edu.sdjzu.xg.bysj.domain.School;
import cn.edu.sdjzu.xg.bysj.domain.University;
import cn.edu.sdjzu.xg.bysj.exception.BysjException;
import cn.edu.sdjzu.xg.bysj.service.UniversityService;
import cn.edu.sdjzu.xg.bysj.util.JdbcHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.TreeSet;

public class SchoolDao {
    private SchoolDao(){}
    private static SchoolDao schoolDao = null;
    // 返回本类的唯一对象
    public static SchoolDao getInstance(){
        // 静态变量schoolDao为null时，才创建SchoolDao对象
        // 第二次或者后来调用本方法schoolDao就不为null了，故不再创建对象
        if(schoolDao == null){
            schoolDao = new SchoolDao();
        }
        return schoolDao;
    }

    public School find(Integer id) throws SQLException, BysjException {
        School desiredSchool = null;
        Connection connection = JdbcHelper.getConn();
        String sql_select_id = "SELECT * FROM school WHERE id=?";
        PreparedStatement pstmt_select_id = connection.prepareStatement(sql_select_id);
        pstmt_select_id.setInt(1,id);
        ResultSet resultSet_select_id = pstmt_select_id.executeQuery();
        // 如果表中存在id对应的元组，则获得表中的字段值，并创建对象
        if (resultSet_select_id.next()){
            String description = resultSet_select_id.getString("description");
            String no = resultSet_select_id.getString("no");
            String remarks = resultSet_select_id.getString("remarks");
            int university_id = resultSet_select_id.getInt("university_id");
            University university = UniversityService.getInstance().find(university_id);
            desiredSchool = new School(id,description,no,remarks,university);
        }
        // 关闭三个资源
        JdbcHelper.close(resultSet_select_id,pstmt_select_id,connection);
        return desiredSchool;
    }

    // 条件查询
    public Collection<School> findAll(List<Condition> conditionList) throws SQLException, BysjException {
        Connection connection = JdbcHelper.getConn();
        Collection<School> desiredSchools = new TreeSet<School>();
        // 创建查询School的主语句
        StringBuilder sql_select = new StringBuilder("select * from school");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(connection,"school");
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        // 执行预编译语句，返回结果集
        ResultSet resultSet = pstmt_select_all.executeQuery();
        // 遍历结果集
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int university_id = resultSet.getInt("university_id");
            University university = UniversityService.getInstance().find(university_id);
            // 以当前运足的各个字段值为参数，创建School对象
            School school = new School(id,description,no,remarks,university);
            // 将当前元组代表的School镀锡添加到desiredSchools集合中
            desiredSchools.add(school);
        }
        JdbcHelper.close(resultSet,pstmt_select_all,connection);
        return desiredSchools;
    }

    // 条件查询与分页查询
        public Collection<School> findAll(List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        Connection connection = JdbcHelper.getConn();
        Collection<School> desiredSchools = new HashSet<School>();
        // 创建查询School的主语句
        StringBuilder sql_select = new StringBuilder("select * from school");
        if (conditionList != null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        // 获取Teacher中的元组总数
        int totalNum = CommonDao.getInstance().count(connection,"school",conditionList);
        String paginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(paginationClause);
        // 获取查询教师的语句对象
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        // 执行预编译语句，返回结果集
        ResultSet resultSet = pstmt_select_all.executeQuery();
        // 遍历结果集
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String description = resultSet.getString("description");
            String no = resultSet.getString("no");
            String remarks = resultSet.getString("remarks");
            int university_id = resultSet.getInt("university_id");
            University university = UniversityService.getInstance().find(university_id);
            // 以当前运足的各个字段值为参数，创建School对象
            School school = new School(id,description,no,remarks,university);
            // 将当前元组代表的School镀锡添加到desiredSchools集合中
            desiredSchools.add(school);
        }
        JdbcHelper.close(resultSet,pstmt_select_all,connection);
        return desiredSchools;
    }

    public boolean delete(int id) throws SQLException, BysjException {
        Connection connection = JdbcHelper.getConn();
        PreparedStatement pr = connection.prepareStatement("delete from school where id=?");
        pr.setInt(1,id);
        int affectedRow = pr.executeUpdate();
        JdbcHelper.close(pr,connection);
        return affectedRow > 0;
    }
    public boolean add(Connection connection, School school) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_insert = "insert into school " +
                "(id,description,no,remarks,university_id) values (?,?,?,?,?)";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setInt(1,school.getId());
        pstmt_insert.setString(2,school.getDescription());
        pstmt_insert.setString(3,school.getNo());
        pstmt_insert.setString(4,school.getRemarks());
        pstmt_insert.setInt(5,school.getUniversity().getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected > 0;
    }
    public boolean update(Connection connection, School school) throws SQLException, BysjException {
        //获取数据库连接
        //准备预编译的语句
        String sql_insert = "update school " +
                "set description=? where id=?";
        PreparedStatement pstmt_insert = connection.prepareStatement(sql_insert);
        pstmt_insert.setString(1,school.getDescription());
        pstmt_insert.setInt(2,school.getId());
        //执行预编译语句，影响的行数保存在rowAffected中
        int rowAffected = pstmt_insert.executeUpdate();
        return rowAffected > 0;
    }
}

